var mysql = require('mysql')

// 配置数据库
var mysql = require('mysql');
var pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'school',
    timezone: "08:00" //从数据库获取的时间日期正常
});

var query = require('../model/query')

/**
 * 
 * @param {*} callback 
 * 获取所有学生数据
 */
function findAll(callback) {
    let sql = 'select * from `student`'
    query(pool, sql).then(function (ret) {
        console.log(ret)
        callback(null, ret)
    }).catch(function (err) {
        callback(err)
    })
}

/**
 * 查询学生信息
 */

function findStudent(json, callback) {

    let sql = 'SELECT * FROM `student` where name like "%' + json.name + '%"'
    if (json.class) {
        // 如果有查询的班级就把班级条件加上
        sql = 'SELECT * FROM `student` where name like "%' + json.name + '%" and class=' + json.class
    }
    console.log('查询到的学生信息sql是', sql)
    query(pool, sql).then(function (ret) {
        console.log('查询到的学生数据是')
        console.log(ret)
        console.log(ret.length)
        if (ret.length > 0) {
            let sid = '('
            ret.forEach(function (val, idx) {
                if (idx < ret.length - 1) {
                    sid += val.sid + ','
                } else {
                    sid += val.sid + ')'
                }
            })
            console.log('sid是')
            console.log(sid)

            sql = 'SELECT * FROM (`student` INNER join `scores` on student.sid=scores.studentId) INNER JOIN examination on scores.type = examination.id and student.sid in ' + sid
            query(pool, sql).then(function (ret) {
                callback(null, ret)
            })
        } else {
            callback(null, ret)
        }
    })
}

/**
 * 根据组号获取学生信息
 * @param {学生组号} json 
 * @param {*} callback 
 */

function findStudentByGroupId(json, callback) {
    if (!json.topicId) {
        console.log('标题为空')
        let sql = 'select * from `student` where `group`=' + json.groupId
        console.log(sql)
        query(pool, sql).then(function (ret) {
            // console.log(ret)
            for (let i = 0; i < ret.length; i++) {
                ret[i].sub = false
                ret[i].isFinish = false
            }
            callback(null, ret)
        }).catch(function (err) {
            callback(err)
        })
    } else {
        console.log('标题不为空')
        let sql = 'select * from `topic` where `id` = ' + json.topicId
        query(pool, sql).then(function (ret) {
            // console.log(ret)
            let studentId = ''
            let proficient = ret[0].proficient
            let good = ret[0].good
            let flunk = ret[0].flunk
            if (proficient) {
                studentId += proficient + ','
            }
            if (good) {
                studentId += good + ','
            }
            if (flunk) {
                studentId += flunk
            }
            if (studentId[studentId.length - 1] == ',') {
                studentId = studentId.substring(0, studentId.length - 1)
            }
            if (studentId) {
                sql = 'select * from `student` where `group`=' + json.groupId + ' and id in (' + studentId + ')'
            } else {
                sql = 'select * from `student` where `group`=' + json.groupId + ' and id in ("' + studentId + '")'
            }
            console.log(sql)
            console.log(studentId)
            query(pool, sql).then(function (ret) {
                // console.log(ret)
                for (let i = 0; i < ret.length; i++) {
                    ret[i].sub = false
                    ret[i].isFinish = true
                }
                let studentArr = ret
                if (studentId) {
                    sql = 'select * from `student` where `group`=' + json.groupId + ' and id not in (' + studentId + ')'
                } else {
                    sql = 'select * from `student` where `group`=' + json.groupId + ' and id not in ("' + studentId + '")'
                }
                console.log(sql)
                query(pool, sql).then(function (ret) {
                    // console.log(ret)
                    for (let i = 0; i < ret.length; i++) {
                        ret[i].sub = false
                        ret[i].isFinish = false
                    }
                    studentArr = studentArr.concat(ret)
                    callback(null, studentArr)
                }).catch(function (err) {
                    callback(err)
                })
            }).catch(function (err) {
                callback(err)
            })
        }).catch(function (err) {
            callback(err)
        })
    }
}

/**
 * 添加学生信息
 * @param {学生基本信息} json 
 * @param {*} callback 
 */
function add(json, callback) {
    console.log(json)
    let sql = 'INSERT student VALUE(NULL, "' + json.name + '", ' + json.class + ')'
    console.log('添加学生信息的sql语句是', sql)

    // 1先把学生信息插入student表
    query(pool, sql).then(function (ret) {
        console.log(ret)
        if (ret) {
            json.studentId = ret.insertId
            sql = 'INSERT scores VALUE(NULL, ' + json.type + ', ' + json.chinese + ', ' + json.math + ', ' + json.english + ', ' + json.politics + ', ' + json.history + ', ' + json.geography + ', ' + json.physics + ', ' + json.chemistry + ', ' + json.biology + ', ' + json.studentId + ', ' + json.totalScore + ')'
            console.log('添加学生成绩数据是', sql)
            // 2再把学生的成绩和学生id插入scores表
            query(pool, sql).then(function (ret) {
                callback(null, 'success')
            }).catch(function (err) {
                callback(err)
            })
        } else {
            callback(null, '添加学生信息失败')
        }
    }).catch(function (err) {
        callback(err)
    })
}

/**
 * 修改学生信息
 * @param {学生信息} json 
 * @param {回调函数} callback 
 */
function update(json, callback) {
    console.log(json)
    let sql = 'UPDATE student SET name="' + json.name + '", class=' + json.class + ' WHERE sid=' + json.sid
    console.log(sql)
    query(pool, sql).then(function (ret) {
        console.log(ret)
        sql = 'UPDATE scores SET type=' + json.type + ', chinese=' + json.chinese + ', math=' + json.math + ', english=' + json.english + ', politics=' + json.politics + ', history=' + json.history + ', geography=' + json.geography + ', physics=' + json.physics + ', chemistry=' + json.chemistry + ', biology=' + json.biology + ', totalScore=' + json.totalScore + ' WHERE studentId=' + json.sid
        console.log('修改成绩表的sql是', sql)
        query(pool, sql).then(function (ret) {
            callback(null, 'success')
        }).catch(function (err) {
            callback(err)
        })
    }).catch(function (err) {
        callback(err)
    })
}

exports.findAll = findAll
exports.add = add
exports.findStudentByGroupId = findStudentByGroupId
exports.update = update
exports.findStudent = findStudent